﻿using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using WineStore.DAL;

namespace WineStore.Models
{
    public class Sim
    {
        public int SimId { get; set; }

        [Required(ErrorMessage = "Bạn phải nhập dữ liệu !")]
        public int CategorySimId { get; set; }

        [Required(ErrorMessage = "Bạn phải nhập dữ liệu !")]
        public int SupplierId { get; set; }

        [Required(ErrorMessage = "Bạn phải nhập dữ liệu !")]
        public string SimNumber { get; set; }

        [Required(ErrorMessage = "Bạn phải nhập dữ liệu !")]
        public string SimPrice { get; set; }

        public string SimNotice { get; set; }

        

        public virtual CategorySim CategorySim { get; set; }

        public virtual Supplier Supplier { get; set; }

        public virtual ICollection<Order> Orders { get; set; }

        


        //

        public static void SaveSims(Worksheet worksheet, SharedStringTable sharedString)
        {
            try
            {
                //Initialize the customer list.
                List<Sim> result = new List<Sim>();

                //LINQ query to skip first row with column names.
                IEnumerable<Row> dataRows = from row in worksheet.Descendants<Row>()
                                            where row.RowIndex > 1
                                            select row;

                WineDbContext db = new WineDbContext();
                List<Sim> simsdb = db.Sims.ToList();
                List<Supplier> suppliers = db.Suppliers.ToList();
                List<CategorySim> categorysims = db.CategorySims.ToList();

                foreach (Row row in dataRows)
                {
                    //LINQ query to return the row's cell values.
                    //Where clause filters out any cells that do not contain a value.
                    //Select returns the value of a cell unless the cell contains
                    //  a Shared String.
                    //If the cell contains a Shared String, its value will be a 
                    //  reference id which will be used to look up the value in the 
                    //  Shared String table.
                    IEnumerable<String> textValues = from cell in row.Descendants<Cell>()
                                                     where cell.CellValue != null
                                                     select (cell.DataType != null && cell.DataType.HasValue && cell.DataType == CellValues.SharedString ? sharedString.ChildElements[int.Parse(cell.CellValue.InnerText)].InnerText : cell.CellValue.InnerText);

                    //Check to verify the row contained data.
                    if (textValues.Count() > 0)
                    {
                        //Create a customer and add it to the list.
                        var textArray = textValues.ToArray();
                        Sim sim = new Sim();

                        string temp = textArray[2];
                        temp = GetSimNumber(temp);

                        if (CheckExist(temp, result) == 0 && CheckExist(temp, simsdb) == 0)
                        {
                            sim.SimNumber = temp;

                            sim.CategorySimId = Convert.ToInt32(textArray[0]);
                            sim.CategorySim = categorysims.Find(c => c.CategorySimId == sim.CategorySimId);

                            sim.SupplierId = Convert.ToInt32(textArray[1]);
                            sim.Supplier = suppliers.Find(s => s.SupplierId == sim.SupplierId);


                            sim.SimPrice = textArray[3];

                            sim.SimNotice = textArray[4];

                           

                            result.Add(sim);
                            db.Sims.Add(sim);
                        }

                    }
                    else
                    {
                        //If no cells, then you have reached the end of the table.
                        break;
                    }
                }
                db.SaveChanges();
                //Return populated list of customers.
                //return db.Phones.ToList();
            }
            catch
            {

            }
            
        }

        //
        public static int CheckExist(string number, List<Sim> result)
        {
            int count = 0;

            foreach (var item in result)
            {
                if (item.SimNumber.Equals(number))
                {
                    count++;
                }
            }

            return count;
        }

        //
        public static string GetSimNumber(string number)
        {
            string result = "";

            for (int i = 0; i < number.Length; i++)
            {
                if (!number[i].Equals('.') && !number[i].Equals(' '))
                {
                    result += number[i];
                }
            }

            return result;
        }
    }
}